/*
 * Copyright (c) MuleSoft, Inc.  All rights reserved.  http://www.mulesoft.com
 * The software in this package is published under the terms of the CPAL v1.0
 * license, a copy of which has been included with this distribution in the
 * LICENSE.txt file.
 */

package org.mule.runtime.module.db.internal.resolver.param;

import org.mule.runtime.module.db.internal.domain.connection.DbConnection;
import org.mule.runtime.module.db.internal.domain.query.QueryTemplate;
import org.mule.runtime.module.db.internal.domain.type.DbType;
import org.mule.runtime.module.db.internal.domain.type.DbTypeManager;
import org.mule.runtime.module.db.internal.domain.type.ResolvedDbType;
import org.mule.runtime.module.db.internal.domain.type.UnknownDbTypeException;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Resolves parameter types for stored procedure queries
 */
public class StoredProcedureParamTypeResolver implements ParamTypeResolver {

  public static final int PARAM_NAME_COLUN_INDEX = 4;
  public static final int TYPE_ID_COLUMN_INDEX = 6;
  public static final int TYPE_NAME_COLUMN_INDEX = 7;

  private static final Logger logger = LoggerFactory.getLogger(StoredProcedureParamTypeResolver.class);

  private final Pattern storedProcedureMatcher = Pattern.compile("(?msi)(\\{\\s+)?call\\s* \\s*(\\w+)\\(.*");
  private final DbTypeManager dbTypeManager;

  public StoredProcedureParamTypeResolver(DbTypeManager dbTypeManager) {
    this.dbTypeManager = dbTypeManager;
  }

  @Override
  public Map<Integer, DbType> getParameterTypes(DbConnection connection, QueryTemplate queryTemplate) throws SQLException {
    DatabaseMetaData dbMetaData = connection.getMetaData();

    String storedProcedureName = getStoredProcedureName(dbMetaData, queryTemplate.getSqlText());
    ResultSet procedureColumns = dbMetaData.getProcedureColumns(connection.getCatalog(), null, storedProcedureName, "%");

    try {
      return getStoredProcedureParamTypes(connection, storedProcedureName, procedureColumns);
    } finally {
      if (procedureColumns != null) {
        procedureColumns.close();
      }
    }
  }

  private Map<Integer, DbType> getStoredProcedureParamTypes(DbConnection connection, String storedProcedureName,
                                                            ResultSet procedureColumns)
      throws SQLException {
    Map<Integer, DbType> paramTypes = new HashMap<Integer, DbType>();

    int position = 1;

    while (procedureColumns.next()) {
      int typeId = procedureColumns.getInt(TYPE_ID_COLUMN_INDEX);
      String typeName = procedureColumns.getString(TYPE_NAME_COLUMN_INDEX);

      if (logger.isDebugEnabled()) {
        String name = procedureColumns.getString(PARAM_NAME_COLUN_INDEX);
        logger.debug(String.format("Resolved parameter type: Store procedure: %s Name: %s Index: %s Type ID: %s Type Name: %s",
                                   storedProcedureName, name, position, typeId, typeName));
      }

      DbType dbType;
      try {
        dbType = dbTypeManager.lookup(connection, typeId, typeName);
      } catch (UnknownDbTypeException e) {
        // Type was not found in the type manager, but the DB knows about it
        dbType = new ResolvedDbType(typeId, typeName);
      }
      paramTypes.put(position, dbType);
      position++;
    }

    return paramTypes;
  }

  private String getStoredProcedureName(DatabaseMetaData dbMetaData, String sqlText) throws SQLException {
    Matcher matcher = storedProcedureMatcher.matcher(sqlText);

    if (!matcher.matches()) {
      throw new SQLException("Unable to detect stored procedure name from " + sqlText);
    }

    String storedProcedureName = matcher.group(2);

    if (dbMetaData.storesUpperCaseIdentifiers()) {
      return storedProcedureName.toUpperCase();
    } else {
      return storedProcedureName;
    }
  }
}
